﻿<%@ WebHandler Language="C#" Class="ApsBaseDataUpdateHandler" %>

using System;
using System.Web;
using Newtonsoft.Json;
using System.Linq;
using System.Web.SessionState;
using System.Collections.Generic;
using BusinessRule.DTO;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Collections;
using System.Web.SessionState;
using BusinessRule.Business;
using Camstar.WebClient.WebServicesProxy.InSiteWebServices;
using DatabaseAccess.DBUtility;
using System.Text.RegularExpressions;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

public class ApsBaseDataUpdateHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    string fileBasePath = "/InSiteWebApplication/Temp2/ApsBaseData/";
    ResourceMaintBusiness resourceBusiness = new ResourceMaintBusiness();//设备类
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    WorkFlowStepMaintBusiness workFlowStepMaint = new WorkFlowStepMaintBusiness();
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "UpdateStepBaseData": //上传工序基础数据
                    if (!Directory.Exists(context.Server.MapPath(fileBasePath)))
                    {
                        Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
                    }
                    UpdateStepBaseData(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    public void UpdateStepBaseData(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除

            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            DeleteFile(fpath);
            ISheet sheet = Workbook.GetSheetAt(0);
            string type = context.Request["modelType"].ToString();
            uMESProcessResult result = new uMESProcessResult();
            switch (type)
            {
                case "0": //上传工序生产周期等基础数据
                    result = GetApsStepBaseData(sheet, IsXls);//获取解析后的数据
                    break;
                case "1"://上传工序设备数据（弃用）
                    result = GetApsStepResourceData(sheet, IsXls);//获取解析后的数据
                    break;
                default:
                    break;
            }

            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 上传工序设备数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="xlsType"></param>
    /// <returns></returns>
    public uMESProcessResult GetApsStepResourceData(ISheet sheet, string xlsType)
    {
        DataTable dtStepRes = new DataTable();
        dtStepRes.Columns.Add("SPECID");
        dtStepRes.Columns.Add("RESOURCEID");
        DataTable dtWorkCenter = new DataTable();
        dtWorkCenter = commonBusiness.GetWorkCenterByFacoryID(m_UserInfo.FactoryID, false);
        uMESProcessResult result = new uMESProcessResult();
        int i = 0;
        IRow row = null;
        IEnumerator rows = sheet.GetRowEnumerator();
        rows.MoveNext();
        while (rows.MoveNext())
        {
            switch (xlsType)
            {
                case ".xlsx":
                    row = (XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空，结束
            {
                break;
            }
            if (string.IsNullOrWhiteSpace(GetCellValue(row.GetCell(0))))
            {
                break;
            }
            string strSql = @"SELECT s.specid FROM workflowstep ws
                  LEFT JOIN Workflow w ON w.workflowid = ws.workflowid
                  LEFT JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                  LEFT JOIN product p ON p.productid = w.productid
                  LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                  LEFT JOIN specbase sb_ ON sb_.specbaseid = ws.specbaseid
                  LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,ws.specid)
                  WHERE ";
            strSql += string.Format(" w.workflowrevision = '{0}'", GetCellValue(row.GetCell(3)));
            strSql += string.Format(" and wb.workflowname = '{0}'", GetCellValue(row.GetCell(2)));
            strSql += string.Format(" and pb.productname = '{0}'", GetCellValue(row.GetCell(0)));
            strSql += string.Format(" and p.productrevision = '{0}'", GetCellValue(row.GetCell(1)));
            string workflowname = GetCellValue(row.GetCell(4)) + "-" + GetCellValue(row.GetCell(5));
            strSql += string.Format(" and ws.workflowstepname like '%{0}%'", workflowname);
            DataTable dt1 = DatabaseHelper.Query_ReturnDatTable(strSql);//查询出工序

            strSql = @"SELECT r.resourceid FROM resourcedef r where r.resourcename='{0}'";
            strSql = string.Format(strSql, GetCellValue(row.GetCell(6)));
            DataTable dt2 = DatabaseHelper.Query_ReturnDatTable(strSql);//查询出设备
            if (dt1.Rows.Count > 0 && dt2.Rows.Count > 0)
            {
                DataRow dr = dtStepRes.NewRow();
                dr["RESOURCEID"] = dt2.Rows[0]["resourceid"].ToString();
                dr["SPECID"] = dt1.Rows[0]["specid"].ToString();
                if (string.IsNullOrWhiteSpace(dr["RESOURCEID"].ToString()) || string.IsNullOrWhiteSpace(dr["SPECID"].ToString()))
                {
                    result.Result = false;
                    result.Message = "导入出错：第" + (i + 1) + "条数据有误，未找到工序或设备";
                    break;
                }
                dtStepRes.Rows.Add(dr);
            }
            else
            {
                result.Result = false;
                result.Message = "导入错误：第" + (i + 1) + "条数据有误，未找到该工序或设备";
                break;
            }
            i++;
        }
        if (result.Result)
        {
            result = SaveStepResDatas(dtStepRes);
            if (result.Result)
            {
                result.Message = "导入数据成功，成功条数：" + i + " 条";
            }
        }
        return result;
    }
    /// <summary>
    /// 保存工序设备数据
    /// </summary>
    /// <returns></returns>
    private uMESProcessResult SaveStepResDatas(DataTable dtStepRes)
    {
        uMESProcessResult result = new uMESProcessResult();
        try
        {
            ArrayList sqlList = new ArrayList();
            string strSql = "";
            foreach (DataRow item in dtStepRes.Rows)
            {
                strSql = String.Format("select * from SpecResourceInfo sri where sri.specid='{0}' and sri.Resourceid='{1}'", item["SPECID"].ToString(), item["RESOURCEID"].ToString());
                DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);
                if (dt.Rows.Count == 0)
                {
                    strSql = "insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') ";
                    strSql = String.Format(strSql, item["RESOURCEID"].ToString(), item["SPECID"].ToString());
                    sqlList.Add(strSql);
                }
            }
            DatabaseHelper.ExecuteSqlTran(sqlList);
            result.Result = true;
        }
        catch (Exception e)
        {
            result.Result = false;
            result.Message = "导入出错，原因：" + e.Message;
        }
        return result;
    }

    /// <summary>
    /// 上传工序生产周期基础数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="xlsType"></param>
    /// <returns></returns>
    public uMESProcessResult GetApsStepBaseData(ISheet sheet, string xlsType)
    {
        uMESProcessResult result = new uMESProcessResult();
        result.Result = true;
        //DataTable dtfattr = commonBusiness.GetFatoryAttributes(m_UserInfo.FactoryID);
        //DataRow[] drs = dtfattr.Select("USERATTRIBUTENAME='分厂简称'");
        //if (drs.Length == 0)
        //{
        //    result.Result = false;
        //    result.Message = "未维护分厂简称属性";
        //    return result;
        //}
        //string factorySimName = drs[0]["ATTRIBUTEVALUE"].ToString();//分厂简称
        DataTable cycDt = new DataTable();//工序基础数据
        cycDt.Columns.Add("PREPARECYCLE");
        cycDt.Columns.Add("MAKECYCLE");
        cycDt.Columns.Add("TRANSFERCYCLE");
        cycDt.Columns.Add("SHIPCYCLE");
        cycDt.Columns.Add("COMMICYCLE");
        cycDt.Columns.Add("MAKEEMPCOUNTS");
        cycDt.Columns.Add("CHARGINGQTY");
        cycDt.Columns.Add("WORKFLOWSTEPID");
        cycDt.Columns.Add("WORKCENTERID");
        cycDt.Columns.Add("TEAMID");
        DataTable dtStepRes = new DataTable();//工序设备关联
        dtStepRes.Columns.Add("SPECID");
        dtStepRes.Columns.Add("RESOURCEID");
        string strSql = @"SELECT ws.workflowstepid,s.factoryid,s.specid,f.factoryname,w.factoryid workflowfacid,NVL(ws.controlcode,s.controlcode) controlcode
                  FROM workflowstep ws
                  LEFT JOIN Workflow w ON w.workflowid = ws.workflowid
                  LEFT JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                  LEFT JOIN productworkflowmapping pwm ON pwm.workflowid = w.workflowid
                  LEFT JOIN product p ON p.productid = pwm.productid
                  LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                  LEFT JOIN specbase sb_ ON sb_.specbaseid = ws.specbaseid
                  LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,ws.specid)
                  LEFT JOIN factory f ON f.factoryid = s.factoryid
                  WHERE ";
        ArrayList sqlResList = new ArrayList();
        int i = 0;
        IRow row = null;
        IEnumerator rows = sheet.GetRowEnumerator();
        rows.MoveNext();
        while (rows.MoveNext())
        {
            switch (xlsType)
            {
                case ".xlsx":
                    row = (XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空，结束
            {
                break;
            }
            //if (string.IsNullOrWhiteSpace(GetCellValue(row.GetCell(14, MissingCellPolicy.CREATE_NULL_AS_BLANK))))
            //{
            //    break;
            //}
            string sqlWhere = "";
            sqlWhere += string.Format(" w.workflowrevision = '{0}'", GetCellValue(row.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
            sqlWhere += string.Format(" and wb.workflowname = '{0}'", GetCellValue(row.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
            sqlWhere += string.Format(" and pb.productname = '{0}'", GetCellValue(row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
            sqlWhere += string.Format(" and p.productrevision = '{0}'", GetCellValue(row.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
            string workflowname = GetCellValue(row.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK)) + "-" + GetCellValue(row.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK));
            sqlWhere += string.Format(" and ws.workflowstepname like '%{0}%'", workflowname);
            DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql + sqlWhere);
            //如果查询不到数据，则换一个查询
            if (dt.Rows.Count == 0)
            {
                strSql = @"SELECT ws.workflowstepid,s.factoryid,s.specid,f.factoryname,w.factoryid workflowfacid,NVL(ws.controlcode,s.controlcode) controlcode
                  FROM workflowstep ws
                  LEFT JOIN Workflow w ON w.workflowid = ws.workflowid
                  LEFT JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                  LEFT JOIN product p ON p.productid = w.productid
                  LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                  LEFT JOIN specbase sb_ ON sb_.specbaseid = ws.specbaseid
                  LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,ws.specid)
                  LEFT JOIN factory f ON f.factoryid = s.factoryid
                  WHERE ";
                dt = DatabaseHelper.Query_ReturnDatTable(strSql + sqlWhere);
                if (dt.Rows.Count == 0)
                {
                    result.Result = false;
                    result.Message = "导入出错：第" + (i + 2) + "条未在系统中找到数据";
                    break;
                }
            }
            string resources = GetCellValue(row.GetCell(14, MissingCellPolicy.CREATE_NULL_AS_BLANK));//设备列表
            //不是转工序或者外委序，则必填设备
            if (string.IsNullOrWhiteSpace(resources) && dt.Rows[0]["factoryid"].ToString() == dt.Rows[0]["workflowfacid"].ToString() && dt.Rows[0]["controlcode"].ToString() != "LY05")
            {
                result.Result = false;
                result.Message = "导入出错：第" + (i + 2) + "条数据没有设备数据";
                break;
            }
            if (dt.Rows.Count > 0)
            {
                string workflowstepId = dt.Rows[0]["workflowstepid"].ToString();
                if (string.IsNullOrWhiteSpace(workflowstepId))
                {
                    result.Result = false;
                    result.Message = "导入出错：第" + (i + 2) + "条数据未找到工序";
                    break;
                }
                string factoryId = dt.Rows[0]["factoryid"].ToString();
                string factoryName = dt.Rows[0]["factoryname"].ToString();
                string workCenterId = "", teamId = "";
                DataTable dtWorkCenter = new DataTable();
                dtWorkCenter = commonBusiness.GetWorkCenterByFacoryID(factoryId, false);
                //if (dtWorkCenter.Rows.Count == 0)
                //{
                //    result.Result = false;
                //    result.Message = "导入出错：第" + (i + 1) + "条数据未找到分厂";
                //    break;
                //}
                if (dtWorkCenter.Rows.Count > 0)
                {
                    DataRow[] drWorkCenter = dtWorkCenter.Select(string.Format("WORKCENTERNAME='{0}'", row.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
                    if (drWorkCenter.Count() > 0)
                    {
                        workCenterId = drWorkCenter[0]["WORKCENTERID"].ToString();
                    }
                    if (!string.IsNullOrWhiteSpace(workCenterId))
                    {
                        DataTable dtTeam = commonBusiness.GetTeam(workCenterId);
                        DataRow[] drTeam = dtTeam.Select(string.Format("TEAMNAME='{0}'", row.GetCell(8, MissingCellPolicy.CREATE_NULL_AS_BLANK)));
                        if (drTeam.Count() > 0)
                        {
                            teamId = drTeam[0]["TEAMID"].ToString();
                        }
                        //if (string.IsNullOrWhiteSpace(teamId))
                        //{
                        //    result.Result = false;
                        //    result.Message = "导入出错：第" + (i + 2) + "条数据未找到班组";
                        //    break;
                        //}
                    }
                    //if (string.IsNullOrWhiteSpace(workCenterId))
                    //{
                    //    result.Result = false;
                    //    result.Message = "导入出错：第" + (i + 2) + "条数据未找到工区";
                    //    break;
                    //}

                }
                DataRow dr = cycDt.NewRow();
                dr["PREPARECYCLE"] = GetCellValue(row.GetCell(9, MissingCellPolicy.CREATE_NULL_AS_BLANK));//准备时间
                dr["MAKECYCLE"] = GetCellValue(row.GetCell(10, MissingCellPolicy.CREATE_NULL_AS_BLANK));//加工时间
                dr["TRANSFERCYCLE"] = GetCellValue(row.GetCell(11, MissingCellPolicy.CREATE_NULL_AS_BLANK));//转运时间
                if (!string.IsNullOrWhiteSpace(dr["PREPARECYCLE"].ToString()))
                {
                    if (dr["PREPARECYCLE"].ToString().Contains("."))
                    {
                        dr["PREPARECYCLE"] = double.Parse(dr["PREPARECYCLE"].ToString()).ToString("0.0");
                    }
                    else
                    {
                        dr["PREPARECYCLE"] = int.Parse(dr["PREPARECYCLE"].ToString());
                    }
                }
                if (!string.IsNullOrWhiteSpace(dr["MAKECYCLE"].ToString()))
                {
                    if (dr["MAKECYCLE"].ToString().Contains("."))
                    {
                        dr["MAKECYCLE"] = double.Parse(dr["MAKECYCLE"].ToString()).ToString("0.0");
                    }
                    else
                    {
                        dr["MAKECYCLE"] = int.Parse(dr["MAKECYCLE"].ToString());
                    }
                }
                if (!string.IsNullOrWhiteSpace(dr["TRANSFERCYCLE"].ToString()))
                {
                    if (dr["TRANSFERCYCLE"].ToString().Contains("."))
                    {
                        dr["TRANSFERCYCLE"] = double.Parse(dr["TRANSFERCYCLE"].ToString()).ToString("0.0");
                    }
                    else
                    {
                        dr["TRANSFERCYCLE"] = int.Parse(dr["TRANSFERCYCLE"].ToString());
                    }
                }
                dr["SHIPCYCLE"] = GetCellValue(row.GetCell(12, MissingCellPolicy.CREATE_NULL_AS_BLANK));//转工周期
                dr["COMMICYCLE"] = GetCellValue(row.GetCell(13, MissingCellPolicy.CREATE_NULL_AS_BLANK));//外委周期
                dr["MAKEEMPCOUNTS"] = GetCellValue(row.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK));//加工人数
                if (string.IsNullOrWhiteSpace(dr["MAKEEMPCOUNTS"].ToString()))
                {
                    result.Result = false;
                    result.Message = "导入出错：第" + (i + 2) + "行加工人数必填";
                    break;
                }
                dr["CHARGINGQTY"] = GetCellValue(row.GetCell(15, MissingCellPolicy.CREATE_NULL_AS_BLANK));//装炉/槽量
                dr["WORKFLOWSTEPID"] = workflowstepId;
                dr["WORKCENTERID"] = workCenterId;
                dr["TEAMID"] = teamId;
                cycDt.Rows.Add(dr);
                if (!string.IsNullOrWhiteSpace(resources))
                {
                    result = GetResourceList(ref sqlResList, resources, dt.Rows[0]["SpecId"].ToString(), factoryName);
                    if (!result.Result)
                    {
                        result.Message = "导入出错：第" + (i + 2) + "条数据设备报错，原因：" + result.Message;
                        break;
                    }
                }
            }
            else
            {
                result.Result = false;
                result.Message = "导入出错：第" + (i + 2) + "条数据有误，未找到该工序";
                break;
            }
            i++;
        }
        if (result.Result)
        {
            result = SaveStepCycleDatas(cycDt, sqlResList);
            if (result.Result)
            {
                result.Message = "导入数据成功，成功条数：" + i + " 条";
            }
        }
        return result;
    }
    /// <summary>
    /// 绑定工序设备
    /// </summary>
    /// <param name="sqlResList"></param>
    /// <param name="resources"></param>
    /// <param name="specId"></param>
    /// <param name="factoryName"></param>
    /// <returns></returns>
    private uMESProcessResult GetResourceList(ref ArrayList sqlResList, string resources, string specId, string factoryName)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        string[] ress = resources.Split('/');
        foreach (string item in ress)
        {
            string strSql = @"SELECT r.resourceid FROM resourcedef r where r.resourcename='" + item.Trim() + "'";
            DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);//查询出设备
            if (dt.Rows.Count > 0) //如果有设备
            {
                strSql = String.Format("select * from SpecResourceInfo sri where sri.specid='{0}' and sri.Resourceid='{1}'", specId, dt.Rows[0]["RESOURCEID"].ToString());
                DataTable dt1 = DatabaseHelper.Query_ReturnDatTable(strSql);
                if (dt1.Rows.Count == 0)
                {
                    strSql = "insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') ";
                    strSql = String.Format(strSql, dt.Rows[0]["RESOURCEID"].ToString(), specId);
                    sqlResList.Add(strSql);
                }
            }
            else
            {
                result.Result = false;
                result.Message = "系统不存在设备";
                break;
                //string msg = "";
                //Dictionary<string, string> dic = new Dictionary<string, string>();
                //dic.Add("resourcename", item.Trim());//设备编码
                //dic.Add("description", "");//设备名称
                //dic.Add("factoryname", factoryName);//
                //dic.Add("userName", m_UserInfo.APIUserName.ToString());//
                //dic.Add("passWord", m_UserInfo.APIPassword.ToString());//
                //result.Result = resourceBusiness.ResourceMaint("create", dic, ref msg);
                //if (!result.Result)
                //{
                //    result.Message = msg;
                //    break;
                //}
                //strSql = @"SELECT r.resourceid FROM resourcedef r where r.resourcename='" + item.Trim() + "'";
                //DataTable dt2 = DatabaseHelper.Query_ReturnDatTable(strSql);//查询出设备
                //if (dt2.Rows.Count > 0) //如果有设备
                //{
                //    strSql = "insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') ";
                //    strSql = String.Format(strSql, dt2.Rows[0]["RESOURCEID"].ToString(), specId);
                //    sqlResList.Add(strSql);
                //}
            }
        }
        return result;
    }

    /// <summary>
    /// 保存生产周期数据
    /// </summary>
    /// <param name="dtCyc"></param>
    /// <param name="sqlResList"></param>
    /// <returns></returns>
    private uMESProcessResult SaveStepCycleDatas(DataTable dtCyc, ArrayList sqlResList)
    {
        uMESProcessResult result = new uMESProcessResult();
        try
        {
            ArrayList sqlList = new ArrayList();
            string strSql = "";
            foreach (DataRow item in dtCyc.Rows)
            {
                strSql = "UPDATE Workflowstep wfs SET ";
                if (!string.IsNullOrWhiteSpace(item["WORKCENTERID"].ToString()))
                {
                    strSql += String.Format("wfs.workcenterid = '{0}',", item["WORKCENTERID"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(item["TEAMID"].ToString()))
                {
                    strSql += String.Format("wfs.teamid = '{0}',", item["TEAMID"].ToString());
                }
                strSql += String.Format("wfs.preparecycle = '{0}',", item["PREPARECYCLE"].ToString());
                strSql += String.Format("wfs.makecycle = '{0}',", item["MAKECYCLE"].ToString());
                strSql += String.Format("wfs.transfercycle = '{0}',", item["TRANSFERCYCLE"].ToString());
                strSql += String.Format("wfs.shipcycle = '{0}',", item["SHIPCYCLE"].ToString());
                strSql += String.Format("wfs.commicycle = '{0}',", item["COMMICYCLE"].ToString());
                if (string.IsNullOrWhiteSpace(item["MAKEEMPCOUNTS"].ToString()))
                {
                    strSql += String.Format("wfs.makeempcounts = null,");
                }
                else
                {
                    strSql += String.Format("wfs.makeempcounts = '{0}',", item["MAKEEMPCOUNTS"].ToString());
                }
                strSql += String.Format("wfs.Chargingqty = '{0}'", item["CHARGINGQTY"].ToString());

                strSql += String.Format(" WHERE wfs.workflowstepid='{0}'", item["WORKFLOWSTEPID"].ToString());
                sqlList.Add(strSql);
            }
            sqlList.AddRange(sqlResList);
            DatabaseHelper.ExecuteSqlTran(sqlList);
            result.Result = true;
        }
        catch (Exception e)
        {
            result.Result = false;
            result.Message = "导入出错，原因：" + e.Message;
        }
        return result;
    }

    /// <summary>
    /// 获取工作薄对象
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public IWorkbook GetWorkbookObj(string filePath)
    {
        FileStream fileStream = null;
        try
        {
            string IsXls = Path.GetExtension(filePath);
            //读取文件流
            fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            fileStream.Position = 0;
            IWorkbook Workbook = null;
            switch (IsXls)
            {
                case ".xlsx":
                    Workbook = new XSSFWorkbook(fileStream);
                    break;
                case ".xls":
                    Workbook = new HSSFWorkbook(fileStream);
                    break;
                default:
                    break;
            }
            fileStream.Close();//关闭文件流
            return Workbook;
        }
        catch (Exception e)
        {
            fileStream.Close();//关闭文件流
            return null;
        }
    }

    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }

    /// <summary>
    /// 获取单元格值
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private string GetCellValue(ICell cell)
    {
        if (cell == null)
        {
            return "";
        }
        if (cell.CellType == CellType.Formula)//判断单元格是否有公式
        {
            cell.SetCellType(CellType.String);
        }
        return cell.ToString().Trim();
    }

}